package com.rybbaby.tss.core.web.dao.imp;

import com.rybbaby.tss.core.bean.PageBean;
import com.rybbaby.tss.core.utils.DBTypeUtil;
import com.rybbaby.tss.core.utils.DateUtil;
import com.rybbaby.tss.core.web.dao.IGenericDao;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.*;
import org.hibernate.criterion.*;
import org.hibernate.transform.ResultTransformer;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.dao.support.DataAccessUtils;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.orm.hibernate5.HibernateTemplate;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;

import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;
import java.lang.InstantiationException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.math.BigDecimal;
import java.sql.*;
import java.text.MessageFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

/**
 * @author 谢进伟
 * @description DAO层泛型基类
 * @createDate 2015-11-10 下午4:24:40
 */
public abstract class GenericDao<T> implements IGenericDao<T> {

    private static final Logger logger = Logger.getLogger(GenericDao.class);

    public GenericDao() {
        super();
    }

    @Autowired
    protected SessionFactory sessionFactory;

    @Autowired
    protected JdbcTemplate jdbcTemplate;

    @Autowired
    protected HibernateTemplate hibernateTemplate;

    @Autowired
    protected NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    /**
     * 有结果集存储过程结果集数据
     **/
    private List<Map<String, Object>> callableStatementResultList = null;

    /**
     * 数据库类型
     */
    public static final String DATABSE_TYPE_MYSQL = "mysql";
    public static final String DATABSE_TYPE_POSTGRE = "postgresql";
    public static final String DATABSE_TYPE_ORACLE = "oracle";
    public static final String DATABSE_TYPE_SQLSERVER = "sqlserver";

    /**
     * 分页SQL
     */
    public static final String MYSQL_SQL = "select * from ( {0}) sel_tab00 limit {1},{2}";
    public static final String POSTGRE_SQL = "select * from ( {0}) sel_tab00 limit {2} offset {1}";
    public static final String ORACLE_SQL = "select * from (select row_.*,rownum rownum_ from ({0}) row_ where rownum <= {1}) where rownum_>{2}";
    public static final String SQLSERVER_SQL = "select * from ( select row_number() over(order by tempColumn) tempRowNumber, * from (select top {1} tempColumn = 0, {0}) t ) tt where tempRowNumber > {2}";

    public Session getSession() {
        // 事务必须是开启的(Required)，否则获取不到
        return this.sessionFactory.getCurrentSession();
    }

    @Override
    public List<T> loadByMultipleId(Class<T> cls, Object[] Ids) {
        return this.getSession().byMultipleIds(cls).multiLoad(Ids);
    }

    @Override
    public List<T> loadByMultipleId(Class<T> cls, List<? extends Serializable> ids) {
        return this.getSession().byMultipleIds(cls).multiLoad(ids);
    }

    /**
     * 根据传入的实体持久化对象
     *
     * @param entity 需要持久化的对象
     */

    @Override
    public Serializable save(T entity) {
        try {
            Serializable id = hibernateTemplate.save(entity);
            hibernateTemplate.flush();
            if (logger.isDebugEnabled()) {
                logger.debug("保存实体成功," + entity.getClass().getName());
            }
            return id;
        } catch (RuntimeException e) {
            logger.error("保存实体异常", e);
            throw e;
        }
    }

    /**
     * 批量保存实体
     *
     * @param entitys 需要保存的实体集合
     */

    @Override
    public boolean jdbcBatchSave(List<T> entitys) throws RuntimeException {
        boolean result = false;
        if (entitys.size() > 0) {
            T t = entitys.get(0);
            try {
                String sql = this.getBatchInsertSql(t);
                String willSaveFieldStr = sql.toUpperCase().split("VALUES")[0];
                willSaveFieldStr = StringUtils.substringBetween(willSaveFieldStr, "(", ")");
                List<Object[]> batchArgs = this.getBatchInsertArgs(entitys, willSaveFieldStr.split(","));
                int[] batchSaveResult = this.jdbcTemplate.batchUpdate(sql, batchArgs);
                if (batchSaveResult != null && batchSaveResult.length > 0) {
                    Arrays.sort(batchSaveResult);
                    result = batchSaveResult[0] > 0;
                } else {
                    result = false;
                }
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }
        return result;
    }

    @Override
    public List<Serializable> hibernateBatchSave(List<T> entitys) throws RuntimeException {
        List<Serializable> ids = new ArrayList<Serializable>();
        Session session = this.getSession();
        for (int i = 0; i < entitys.size(); i++) {
            Serializable id = this.save(entitys.get(i));
            ids.add(id);
            if (i % 20 == 0) {
                // 20个对象后才清理缓存，写入数据库
                session.flush();
                session.clear();
            }
        }
        // 最后清理一下----防止大于20小于40的不保存
        session.flush();
        session.clear();
        return ids;
    }

    @Override
    public boolean batchUpdate(List<T> entitys) throws RuntimeException {
        return batchUpdate(entitys, true);
    }

    /**
     * 批量修改实体
     *
     * @param entitys 需要修改的实体集合
     */
    @Override
    public boolean batchUpdate(List<T> entitys, boolean isUpdateNullField) throws RuntimeException {
        boolean result = false;
        if (entitys.size() > 0) {
            try {
                List<Object[]> batchArgs = this.getBatchUpdateArgs(entitys, isUpdateNullField);
                if (isUpdateNullField) {
                    T t = entitys.get(0);
                    String sql = this.getBatchUpdateSql(t, isUpdateNullField);
                    result = batchUpdate(sql, batchArgs);
                } else {
                    int size = entitys.size();
                    String sqls[] = new String[size];
                    for (int i = 0; i < size; i++) {
                        T t = entitys.get(i);
                        String sql = this.getBatchUpdateSql(t, isUpdateNullField);
                        StringBuffer realSql = new StringBuffer();
                        String[] sqlChunks = StringUtils.split(sql, "?");
                        Object[] batchArg = batchArgs.get(i);
                        for (int j = 0, len = batchArg.length; j < len; j++) {
                            realSql.append(sqlChunks[j]).append("'").append(StringEscapeUtils.escapeSql(batchArg[j].toString())).append("'");
                        }
                        sqls[i] = realSql.toString();
                    }
                    result = batchUpdate(sqls);
                }
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }
        return result;
    }

    @Override
    public boolean batchUpdate(String sql, List<Object[]> batchArgs) {
        boolean result = false;
        if (batchArgs != null && batchArgs.size() > 0) {
            int[] batchSaveResult = this.jdbcTemplate.batchUpdate(sql, batchArgs);
            if (batchSaveResult != null && batchSaveResult.length > 0) {
                Arrays.sort(batchSaveResult);
                result = batchSaveResult[0] > 0;
            } else {
                result = false;
            }
        }
        return result;
    }

    @Override
    public boolean batchUpdate(String... sqls) {
        boolean result = false;
        if (sqls != null && sqls.length > 0) {
            int[] batchSaveResult = this.jdbcTemplate.batchUpdate(sqls);
            if (batchSaveResult != null && batchSaveResult.length > 0) {
                Arrays.sort(batchSaveResult);
                result = batchSaveResult[0] > 0;
            } else {
                result = false;
            }
        }
        return result;
    }

    /**
     * 批量删除记录
     *
     * @param cls 要删除的类
     * @param ids 需要删除的记录主键集合
     */

    @Override
    public boolean batchDelete(Class<T> cls, Integer ids[]) throws RuntimeException {
        return batchDelete(cls, StringUtils.join(ids, ","));
    }

    /**
     * 批量删除记录
     *
     * @param cls 要删除的类
     * @param ids 需要删除的记录主键集合
     */

    @Override
    public boolean batchDelete(Class<T> cls, String ids[]) throws RuntimeException {
        String idsStr = "'" + StringUtils.join(ids, "','") + "'";
        return this.batchDelete(cls, idsStr);
    }

    /**
     * 持久化一个对象
     *
     * @param entity 需要持久化的对象
     */

    @Override
    public void saveOrUpdate(T entity) {
        try {
            hibernateTemplate.saveOrUpdate(entity);
            if (logger.isDebugEnabled())
                logger.debug("添加或更新成功," + entity.getClass().getName());
        } catch (RuntimeException e) {
            logger.error("添加或更新异常", e);
            throw e;
        }
    }

    /**
     * 删除一个对象
     *
     * @param entity 需要删除的对象
     */

    @Override
    public void delete(T entity) {
        try {
            hibernateTemplate.delete(entity);
            hibernateTemplate.flush();
            if (logger.isDebugEnabled())
                logger.debug("删除成功," + entity.getClass().getName());
        } catch (RuntimeException e) {
            logger.error("删除异常", e);
            throw e;
        }
    }

    /**
     * 删除一个实体类指定Id的对象
     *
     * @param cls 实体
     * @param id  主键Id
     */

    @Override
    public void deleteEntityById(Class<T> cls, Serializable id) {
        T entity = get(cls, id);
        if (entity != null) {
            delete(entity);
        }
    }

    /**
     * 批量删除对象
     *
     * @param entitys 需要删除的对象集合
     */

    @Override
    public void batchDelete(Collection<T> entitys) {
        this.hibernateTemplate.deleteAll(entitys);
    }

    /**
     * 修改一个实体类
     *
     * @param entity 需要修改的实体类对象
     */

    @Override
    public void updateEntitiy(T entity) {
        hibernateTemplate.update(entity);
    }

    /**
     * 通过sql语句更新数据
     *
     * @param sql 更新类型sql语句
     *            （新增、删除、删除）
     */
    @Override
    public int updateBySql(String sql) {
        Query querys = this.getSession().createSQLQuery(sql);
        return querys.executeUpdate();
    }

    /**
     * 加载某一个实体的所有数据
     *
     * @param cls 实体类
     */
    @SuppressWarnings({"unchecked"})
    @Override
    public List<T> loadAll(Class<T> cls) {
        Criteria criteria = createCriteria(cls);
        if (criteria != null) {
            return criteria.list();
        }
        return null;
    }

    /**
     * 使用占位符的方式填充值 请注意：like对应的值格式："%"+username+"%" Hibernate Query
     *
     * @param hql    hql语句
     * @param values 占位符号?对应的值，顺序必须一一对应 可以为空对象数组，但是不能为null
     */
    @SuppressWarnings({"unchecked"})
    @Override
    public List<T> executeQuery(String hql, Object[] values) {
        Query query = this.getSession().createQuery(hql);
        for (int i = 0; (values != null) && (i < values.length); ++i) {
            query.setParameter(i, values[i]);
        }
        return query.list();
    }

    /**
     * 执行存储过程(无结集)
     *
     * @param proc   存储过程名称
     * @param params 存储过程参数,如：<br>
     *               new SqlInOutParameter("inOutName" , Types.VARCHAR)
     *               表示参数“inOutName”为输入输出参数<br>
     *               new SqlOutParameter("outId" , Types.INTEGER)
     *               表示参数“outId”为输出参数<br>
     * @return
     */
    @Override
    public Map<String, Object> callableStatementByName(String proc, List<SqlParameter> params) {
        final StringBuffer callProcedureSql = new StringBuffer("{call " + proc + "(");
        List<String> paramPlaceholder = new ArrayList<String>();
        for (int i = 0; i < params.size(); i++) {
            paramPlaceholder.add("?");
        }
        callProcedureSql.append(StringUtils.join(paramPlaceholder, ",") + ")}");
        Map<String, Object> outValues = jdbcTemplate.call(new CallableStatementCreator() {

            @Override
            public CallableStatement createCallableStatement(Connection conn) throws SQLException {
                return conn.prepareCall(callProcedureSql.toString());
            }
        }, params);
        return outValues;
    }

    /**
     * 执行存储过程(有结果集)
     *
     * @param proc     存储过程名称
     * @param params   存储过程参数,如：<br>
     *                 new SqlInOutParameter("inOutName" , Types.VARCHAR)
     *                 表示参数“inOutName”为输入输出参数<br>
     *                 new SqlOutParameter("outId" , Types.INTEGER)
     *                 表示参数“outId”为输出参数<br>
     * @param outParam 除结果集外的输出参数容器，如果输出参数不需要接收可传入null
     * @return
     */
    @Override
    public List<Map<String, Object>> callableStatementByName(String proc, List<SqlParameter> params, Map<String, Object> outParam) {
        callableStatementResultList = new ArrayList<Map<String, Object>>();
        final StringBuffer callProcedureSql = new StringBuffer("{call " + proc + "(");
        List<String> paramPlaceholder = new ArrayList<String>();
        for (int i = 0; i < params.size(); i++) {
            paramPlaceholder.add("?");
        }
        callProcedureSql.append(StringUtils.join(paramPlaceholder, ",") + ")}");
        Map<String, Object> outValues = jdbcTemplate.call(new CallableStatementCreator() {

            @Override
            public CallableStatement createCallableStatement(Connection conn) throws SQLException {
                CallableStatement prepareCall = conn.prepareCall(callProcedureSql.toString());
                ResultSet rs = null;
                try {
                    // 封装结果集
                    rs = prepareCall.getResultSet();
                    ResultSetMetaData rsmd = rs.getMetaData();// 获取元数据
                    while (rs.next()) {
                        Map<String, Object> map = new HashMap<String, Object>();
                        for (int j = 1; j <= rsmd.getColumnCount(); j++) {
                            map.put(rsmd.getColumnLabel(j), rs.getString(j));
                        }
                        callableStatementResultList.add(map);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    rs.close();
                }
                return prepareCall;
            }
        }, params);
        if (outParam != null) {
            outParam = outValues;
        }
        return callableStatementResultList;
    }

    /**
     * 根据Id获取一个实体对象
     *
     * @param cls 实体类
     * @param id  主键id
     */
    @Override
    public T get(Class<T> cls, Serializable id) {
        return (T) hibernateTemplate.get(cls, id);
    }

    /**
     * 通过hql 查询语句查找对象,并以键值对返回
     *
     * @param hql hql语句
     * @return
     */
    @SuppressWarnings("unchecked")
    @Override
    public Map<Object, Object> getHashMapbyHql(String hql) {
        Query query = this.getSession().createQuery(hql);
        List<Object[]> list = query.list();
        Map<Object, Object> map = new HashMap<Object, Object>();
        for (Iterator<Object[]> iterator = list.iterator(); iterator.hasNext(); ) {
            Object[] tm = (Object[]) iterator.next();
            map.put(tm[0].toString(), tm[1].toString());
        }
        return map;
    }

    /**
     * 获取实体对应表或试图中的记录总数
     *
     * @param cls 实体类
     * @return
     */

    @Override
    public int getTableDataCount(Class<T> cls) {
        int count = DataAccessUtils.intResult(this.getSession().createQuery("select count(1) from " + cls.getName()).list());
        return count;
    }

    /**
     * 执行更新操作，并返回受影响行数
     *
     * @param sql    sql语句
     * @param params 参数
     */
    @Override
    public Integer executeSql(String sql, List<Object> params) {
        return Integer.valueOf(this.jdbcTemplate.update(sql, new Object[]{params}));
    }

    /**
     * 执行更新操作，并返回受影响行数
     *
     * @param sql    sql语句
     * @param params 参数
     */
    @Override
    public Integer executeSql(String sql, Object[] params) {
        return Integer.valueOf(this.jdbcTemplate.update(sql, params));
    }

    /**
     * 执行更新操作，并返回受影响行数
     *
     * @param sql    sql语句
     * @param params 参数
     */
    @Override
    public Integer executeSql(String sql, Map<String, Object> params) {
        return Integer.valueOf(this.namedParameterJdbcTemplate.update(sql, params));
    }

    /**
     * 执行更新操作，并返回受影响的行数
     *
     * @param sql    sql语句
     * @param params 参数
     */
    @Override
    public Object executeSqlReturnKey(String sql, Map<String, Object> params) {
        Object keyValue = null;
        KeyHolder keyHolder = new GeneratedKeyHolder();
        SqlParameterSource sqlp = new MapSqlParameterSource(params);
        this.namedParameterJdbcTemplate.update(sql, sqlp, keyHolder);
        keyValue = Long.valueOf(keyHolder.getKey().longValue());
        return keyValue;
    }

    /**
     * 执行hql更新数据，并返回受影响行数
     *
     * @param hql hql语句
     */
    @Override
    public Integer executeHql(String hql) {
        Query q = this.getSession().createQuery(hql);
        return Integer.valueOf(q.executeUpdate());
    }

    /**
     * 执行hql语句查询查找对象
     *
     * @param hql    hql语句
     * @param params 参数
     */
    @SuppressWarnings({"unchecked"})
    @Override
    public List<T> findByHql(String hql, Object[] params) {
        Query q = this.getSession().createQuery(hql);
        if ((params != null) && (params.length > 0)) {
            for (int i = 0; i < params.length; ++i) {
                q.setParameter(i, params[i]);
            }
        }
        return q.list();
    }

    /**
     * 根据属性值该实体的唯一记录，如果有多条记录会报错（抛出NonUniqueResultException异常）
     *
     * @param entityClass  实体类
     * @param propertyName 属性名
     * @param value        属性值
     * @return
     */
    @SuppressWarnings({"unchecked"})
    @Override
    public T findUniqueByProperty(Class<T> entityClass, String propertyName, Object value) {
        Assert.hasText(propertyName, "'propertyName' must not be empty");
        if (value != null) {
            Object o = createCriteria(entityClass, new Criterion[]{Restrictions.eq(propertyName, value)}).uniqueResult();
            if (o != null) {
                return (T) o;
            }
        } else {
            Object o = createCriteria(entityClass, new Criterion[]{Restrictions.isNull(propertyName)}).uniqueResult();
            if (o != null) {
                return (T) o;
            }
        }
        return null;
    }

    /**
     * 根据属性值该实体类的记录
     *
     * @param entityClass  实体类
     * @param propertyName 属性名
     * @param value        属性值
     * @return
     */
    @SuppressWarnings({"unchecked"})
    @Override
    public List<T> findByProperty(Class<T> entityClass, String propertyName, Object value) {
        Assert.hasText(propertyName, "'propertyName' must not be empty");
        if (value != null) {
            return createCriteria(entityClass, new Criterion[]{Restrictions.eq(propertyName, value)}).list();
        } else {
            return createCriteria(entityClass, new Criterion[]{Restrictions.isNull(propertyName)}).list();
        }
    }

    /**
     * 通过hql 查询语句查找对象，并以实体类型集合返回
     *
     * @param hql hql语句
     * @return
     */
    @SuppressWarnings({"unchecked"})
    @Override
    public List<T> findByHql(String hql) {
        Query queryObject = this.getSession().createQuery(hql);
        List<T> list = queryObject.list();
        if (list.size() > 0) {
            this.getSession().flush();
        }
        return list;
    }

    /**
     * 通过sql查询语句查找对象
     *
     * @param sql    查询类型sql语句，sql语句中的查询库字段别名，必须是cls参数中存在的属性名
     * @param cls    封装查询数据的对象（此参数可以不受Hibernate管制，普通的pojo即可）
     * @param params SQL语句中的参数，请注意参数与sql语句中参数占位符?的对应关系
     * @return
     * @throws Exception
     * @throws IllegalAccessException
     * @throws InstantiationException
     */
    @SuppressWarnings("unchecked")
    @Override
    public List<T> findListBySql(String sql, Class<T> cls, Object... params) throws RuntimeException {
        List<T> resultList = new ArrayList<T>();
        Field fields[] = cls.getDeclaredFields();
        List<Map<String, Object>> list = this.findForJdbc(sql, params);
        try {
            for (Object object : list) {
                T t = cls.newInstance();
                if (object instanceof Map) {
                    Map<String, Object> map = (Map<String, Object>) object;
                    Set<String> columnKeySet = map.keySet();
                    for (Field f : fields) {
                        String fieldName = f.getName();
                        if (columnKeySet.contains(fieldName)) {
                            f.setAccessible(true);
                            Object fieldValue = f.get(t);
                            Object values_obj = map.get(fieldName);
                            if (values_obj != null && !values_obj.equals(fieldValue)) {
                                Class<?> fieldType = f.getType();
                                String fieldTypeName = fieldType.getSimpleName();
                                String value_str = values_obj + "";
                                /**
                                 * 如果是基本数据类型时（如int、float、double、byte、char、
                                 * boolean）
                                 * 需要先将Object转换成相应的封装类之后再转换成对应的基本数据类型
                                 * 否则会报 ClassCastException
                                 **/
                                if (fieldTypeName.equals("BigDecimal")) {
                                    f.set(t, new BigDecimal(value_str));
                                } else if (fieldTypeName.toLowerCase().equals("float")) {
                                    f.set(t, Float.valueOf(value_str));
                                } else if (fieldTypeName.toLowerCase().equals("byte")) {
                                    f.set(t, Byte.valueOf(value_str));
                                } else if (fieldTypeName.toLowerCase().equals("short")) {
                                    f.set(t, Short.valueOf(value_str));
                                } else if (fieldTypeName.toLowerCase().equals("double")) {
                                    f.set(t, Double.valueOf(value_str));
                                } else if (fieldTypeName.toLowerCase().equals("boolean")) {
                                    f.set(t, Boolean.valueOf(value_str));
                                } else if (fieldTypeName.toLowerCase().equals("long")) {
                                    f.set(t, Long.valueOf(value_str));
                                } else if (fieldTypeName.toLowerCase().equals("int") || fieldTypeName.equals("Integer")) {
                                    f.set(t, Integer.valueOf(value_str));
                                } else if (fieldTypeName.toLowerCase().equals("char") || fieldTypeName.equals("Character")) {
                                    f.set(t, Character.valueOf(value_str.charAt(0)));
                                } else if (fieldTypeName.equals("Date")) {
                                    f.set(t, DateUtil.parse(value_str));
                                } else if (fieldTypeName.equals("Timestamp")) {
                                    f.set(t, new Timestamp(DateUtil.parse(value_str).getTime()));
                                } else if (fieldTypeName.equals("Time")) {
                                    f.set(t, new Time(DateUtil.parse(value_str,"HH:mm:ss").getTime()));
                                } else{
                                    f.set(t, String.valueOf(value_str));
                                }
                            }
                        }
                    }
                }
                resultList.add(t);
            }
        } catch (Exception e) {
            resultList.clear();
            throw new RuntimeException(e);
        }
        return resultList;
    }

    /**
     * 将原生sql返回的结果集注入到指定的可不受Hibernate管制实体bean中（待测试）
     *
     * @param sql           查询语句
     * @param columnAliases 手动指定需要返回的数据库字段别名，别名必须是cls参数中存在的属性名
     * @param cls           封装查询数据的对象（此参数可以不受Hibernate管制，普通的pojo即可）
     * @param params        SQL语句中的参数，请注意参数与sql语句中参数占位符?的对应关系
     * @return
     */
    @SuppressWarnings({"unchecked"})
    @Override
    public List<T> findListBySql(String sql, String[] columnAliases, Class<T> cls, Object... params) {
        SQLQuery sqlQuery = this.getSession().createSQLQuery(sql);
        if (params != null && params.length > 0) {
            for (int i = 0; i < params.length; i++) {
                sqlQuery.setParameter(i, params[i]);// 设置参数
            }
        }
        for (String columnAlias : columnAliases) {
            sqlQuery.addScalar(columnAlias);// 添加需要返回的字段别名
        }
        ResultTransformer aliasToBean = Transformers.aliasToBean(cls);
        sqlQuery.setResultTransformer(aliasToBean);
        return sqlQuery.list();
    }

    /**
     * 根据属性值该实体类的记录
     *
     * @param cls          实体类
     * @param propertyName 属性名
     * @param value        属性值
     * @param isAsc        是否升序排列
     */
    @SuppressWarnings({"unchecked"})
    @Override
    public List<T> findByPropertyisOrder(Class<T> cls, String propertyName, Object value, boolean isAsc) {
        Assert.hasText(propertyName, "'propertyName' must not be empty");
        return createCriteria(cls, isAsc, new Criterion[]{Restrictions.eq(propertyName, value)}).list();
    }

    /**
     * 执行查询操作，并将结果集映射到map中<br>
     * <span style="color:red;">返回的结果集大小必须是1</span>
     *
     * @param sql    sql语句
     * @param params 参数
     */
    @Override
    public Map<String, Object> findOneForJdbc(String sql, Object[] params) {
        try {
            return this.jdbcTemplate.queryForMap(sql, params);
        } catch (EmptyResultDataAccessException e) {
        }
        return null;
    }

    /**
     * 执行sql查询，并返回结果集
     *
     * @param sql    sql语句
     * @param params 参数
     */
    @Override
    public List<Map<String, Object>> findForJdbc(String sql, Object[] params) {
        return this.jdbcTemplate.queryForList(sql, params);
    }

    /**
     * 通过DetachedCriteria查询数据集
     *
     * @param dc 查询条件对象
     */
    @SuppressWarnings({"unchecked"})
    @Override
    public List<T> findByDetached(DetachedCriteria dc) {
        Session session = this.getSession();
        return dc.getExecutableCriteria(session).list();
    }

    /**
     * 通过DetachedCriteria查询指定下标开始查询指定数据量的的数据集
     *
     * @param dc       查询条件对象
     * @param pageNo   当前页
     * @param pageSize 的最大数量的结果
     */
    @SuppressWarnings({"unchecked"})
    @Override
    public PageBean pageList(DetachedCriteria dc, int pageNo, int pageSize) {
        Criteria criteria = dc.getExecutableCriteria(this.getSession());
        // 获取总记录数
        Object count = criteria.setProjection(Projections.rowCount()).uniqueResult();
        int sumCount = count == null ? 0 : Integer.parseInt(count.toString());
        // 设置投影为空,并进行分页查询
        criteria.setProjection(null);
        criteria.setResultTransformer(CriteriaSpecification.ROOT_ENTITY);
        criteria.setFirstResult((pageNo - 1) * pageSize);
        criteria.setMaxResults(pageSize);
        List<T> list = criteria.list();
        sumCount = sumCount <= 0 ? 1 : sumCount;
        return new PageBean(pageNo, pageSize, sumCount, list);
    }

    /**
     * 使用指定的sql语句检索数据并分页返回数据
     *
     * @param sql      要执行查询的sql，此sql只需要专注查询，不需要写分页相关的代码
     * @param pageNo   当前页
     * @param pageSize 每一页显示的记录数
     * @return
     */
    @Override
    public PageBean pageList(String sql, int pageNo, int pageSize) {
        // 创建分页SQL
        String sql_results = "select count(1) count from (" + sql + ") tempTable";
        Map<String, Object> map = this.findOneForJdbc(sql_results, null);
        int sumCount = map == null ? 0 : Integer.parseInt(map.get("count") == null ? "0" : map.get("count").toString());
        sumCount = sumCount <= 0 ? 1 : sumCount;
        sql = createPageSql(sql, pageNo, pageSize);
        List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql);
        return new PageBean(pageNo, pageSize, sumCount, list);
    }

    /**
     * 按照数据库类型，将sql格式化为分页SQL
     *
     * @param sql      需要转换成分页的sql语句
     * @param pageNo   当前页
     * @param pageSize 每一页显示的记录数
     * @return
     */
    private String createPageSql(String sql, int pageNo, int pageSize) {
        int beginNum = (pageNo - 1) * pageSize;
        Object[] sqlParam = new String[3];
        sqlParam[0] = sql;
        sqlParam[1] = beginNum + "";
        sqlParam[2] = pageSize + "";
        if (DBTypeUtil.getDBType().toLowerCase().indexOf(DATABSE_TYPE_MYSQL) != -1) {
            sql = MessageFormat.format(MYSQL_SQL, sqlParam);
        } else if (DBTypeUtil.getDBType().toLowerCase().indexOf(DATABSE_TYPE_POSTGRE) != -1) {
            sql = MessageFormat.format(POSTGRE_SQL, sqlParam);
        } else {
            int beginIndex = (pageNo - 1) * pageSize;
            int endIndex = beginIndex + pageSize;
            sqlParam[2] = Integer.toString(beginIndex);
            sqlParam[1] = Integer.toString(endIndex);
            if (DBTypeUtil.getDBType().toLowerCase().indexOf(DATABSE_TYPE_ORACLE) != -1) {
                sql = MessageFormat.format(ORACLE_SQL, sqlParam);
            } else if (DBTypeUtil.getDBType().toLowerCase().indexOf(DATABSE_TYPE_SQLSERVER) != -1) {
                sqlParam[0] = sql.substring(getAfterSelectInsertPoint(sql));
                sql = MessageFormat.format(SQLSERVER_SQL, sqlParam);
            }
        }
        return sql;
    }

    private static int getAfterSelectInsertPoint(String sql) {
        int selectIndex = sql.toLowerCase().indexOf("select");
        int selectDistinctIndex = sql.toLowerCase().indexOf("select distinct");
        return selectIndex + (selectDistinctIndex == selectIndex ? 15 : 6);
    }

    /**
     * 批量删除记录
     *
     * @param cls 要删除的实体
     * @param ids 要删除记录的Id，格式如：1,3,4,5....
     * @return
     * @throws Exception
     */

    private boolean batchDelete(Class<T> cls, String ids) throws RuntimeException {
        boolean result = false;
        if (StringUtils.isNotEmpty(ids)) {
            try {
                Field[] fields = cls.getDeclaredFields();
                Method method = null;
                Id id = null;
                String idColumnName = null;
                for (Field field : fields) {
                    method = getMethod(cls, field);
                    id = null;// 主键
                    id = getId(method, field);
                    if (id != null) {
                        Column column = this.getColumn(method, field);
                        if (column != null) {
                            idColumnName = field.getName();
                        }
                        break;
                    }
                }
                if (idColumnName == null) {
                    throw new Exception(cls.getName() + " 未设置主键!");
                }
                DetachedCriteria dc = DetachedCriteria.forClass(cls);
                Object[] idss = ids.split(",");
                Criterion in = Restrictions.in(idColumnName, idss);
                dc.add(in);
                List<?> entities = findByDetached(dc);
                if (entities != null && !entities.isEmpty()) {
                    this.hibernateTemplate.deleteAll(entities);
                }
                result = true;
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }
        return result;
    }

    private Criteria createCriteria(Class<T> entityClass, boolean isAsc, Criterion[] criterions) {
        Criteria criteria = createCriteria(entityClass, criterions);
        if (isAsc)
            criteria.addOrder(Order.asc("asc"));
        else {
            criteria.addOrder(Order.desc("desc"));
        }
        return criteria;
    }

    private Criteria createCriteria(Class<T> entityClass, Criterion[] criterions) {
        Criteria criteria = this.getSession().createCriteria(entityClass);
        for (Criterion c : criterions) {
            criteria.add(c);
        }
        return criteria;
    }

    private Criteria createCriteria(Class<T> entityClass) {
        if (this.getSession() != null) {
            Criteria criteria = this.getSession().createCriteria(entityClass);
            return criteria;
        }
        return null;
    }

    /**
     * 获取某一实体类的插入sql语句
     *
     * @param t 实体对象
     * @return
     * @throws Exception
     */
    private String getBatchInsertSql(T t) throws Exception {
        Class<?> clazz = t.getClass();
        StringBuilder sql = new StringBuilder();
        StringBuilder insertSql = new StringBuilder();
        StringBuilder valuessql = new StringBuilder();
        if (!clazz.isAnnotationPresent(Table.class)) {
            sql.append("INSERT INTO " + clazz.getName());
        } else {
            Table table = (Table) clazz.getAnnotation(Table.class);
            if ("".equals(table.name())) {
                sql.append("INSERT INTO " + clazz.getName());
            } else {
                sql.append("INSERT INTO " + table.name());
            }
        }
        Field[] fields = clazz.getDeclaredFields();
        Method method;
        insertSql.append(" (");
        valuessql.append(" values ( ");
        for (Field field : fields) {
            method = getMethod(clazz, field);
            if (method != null) {
                Transactional transactionalNmae = null;// 是否为临时属性
                try {
                    transactionalNmae = method.getAnnotation(Transactional.class);
                } catch (Exception e) {
                    transactionalNmae = field.getAnnotation(Transactional.class);
                }
                if (transactionalNmae != null) {
                    continue;
                }
                Column columnName = null;
                try {
                    columnName = (Column) method.getAnnotation(Column.class);
                    if (columnName == null) {
                        columnName = field.getAnnotation(Column.class);
                    }
                } catch (Exception e) {
                    columnName = field.getAnnotation(Column.class);
                }
                if (columnName != null) {
                    field.setAccessible(true);
                    Object value = field.get(t);
                    if (value != null) {
                        if (method.isAnnotationPresent(Table.class)) {
                            if ("".equals(columnName.name())) {
                                insertSql.append(field.getName() + ",");
                            } else {
                                insertSql.append(columnName.name() + ",");
                            }
                        } else {
                            insertSql.append(field.getName() + ",");
                        }
                        valuessql.append("?,");
                    }
                }
            }
        }
        sql.append(insertSql.substring(0, insertSql.length() - 1)).append(") ").append(valuessql.substring(0, valuessql.length() - 1)).append(" )");
        logger.info(sql);
        return sql.toString();
    }

    /**
     * 获取某一实体类的修改的sql语句
     *
     * @param t 实体对象
     * @return
     * @throws Exception
     */

    private String getBatchUpdateSql(T t, boolean isUpdateNullField) throws Exception {
        Class<?> clazz = t.getClass();
        StringBuilder sql = new StringBuilder();
        StringBuilder set = new StringBuilder();
        StringBuilder wheresql = new StringBuilder();
        Method method = null;
        if (!clazz.isAnnotationPresent(Table.class)) {
            sql.append("UPDATE " + clazz.getName());
        } else {
            Table table = (Table) clazz.getAnnotation(Table.class);
            if ("".equals(table.name())) {
                sql.append("UPDATE " + clazz.getName());
            } else {
                sql.append("UPDATE " + table.name());
            }
        }
        Field[] fields = clazz.getDeclaredFields();
        set.append(" set ");
        wheresql.append(" where 1=1 ");
        for (Field field : fields) {
            method = getMethod(clazz, field);
            field.setAccessible(true);
            if ((method != null && method.isAnnotationPresent(Column.class)) || field.isAnnotationPresent(Column.class)) {
                Transactional transactional = null;// 是否为临时属性
                transactional = getTransactional(method, field);
                if (transactional != null) {
                    continue;
                }
                Column columnName = getColumn(method, field);
                Id id = getId(method, field);// 主键
                if (id == null) {
                    Object value = field.get(t);
                    if (value != null || isUpdateNullField) {
                        if ("".equals(columnName.name())) {
                            set.append(" " + field.getName() + " = ?,");
                        } else {
                            set.append(" " + columnName.name() + " = ?,");
                        }
                    } else {
                        if ("".equals(columnName.name())) {
                            set.append(" " + field.getName() + " = " + field.getName() + ",");
                        } else {
                            set.append(" " + columnName.name() + " = " + columnName.name() + ",");
                        }
                    }
                } else {
                    wheresql.append(" and " + columnName.name() + " = ?,");
                }
            }
        }
        if (" where 1=1 ".equals(wheresql)) {
            throw new RuntimeException("实体变量没有设置ID字段值");
        }
        sql.append(set.substring(0, set.length() - 1)).append(wheresql.subSequence(0, wheresql.length() - 1));
        logger.info(sql);
        return sql.toString();
    }

    /**
     * 获取某一属性的Id注解
     *
     * @param method 该属性的getter方法
     * @param field  属性
     * @return
     */
    protected Id getId(Method method, Field field) {
        Id id = null;
        if (method != null) {
            id = method.getAnnotation(Id.class);
        }
        if (id == null) {
            id = field.getAnnotation(Id.class);
        }
        return id;
    }

    /**
     * @param method
     * @param field
     * @return
     */
    private Transactional getTransactional(Method method, Field field) {
        Transactional transactional;
        try {
            transactional = method.getAnnotation(Transactional.class);
            if (transactional == null) {
                transactional = field.getAnnotation(Transactional.class);
            }
        } catch (Exception e) {
            transactional = field.getAnnotation(Transactional.class);
        }
        return transactional;
    }

    /**
     * getBatchSaveArgs
     * 获取批量新建对象集合中与数据库对应的字段的值
     *
     * @param entitys            实体对象集合
     * @param dataBaseColumnName
     * @return
     * @throws Exception
     */
    private List<Object[]> getBatchInsertArgs(List<T> entitys, String[] dataBaseColumnName) throws Exception {
        List<Object[]> batchArgs = new ArrayList<Object[]>();
        Class<?> clazz = null;
        if (entitys.size() > 0) {
            clazz = entitys.get(0).getClass();
        } else {
            return batchArgs;
        }
        if (clazz.isAnnotationPresent(Table.class)) {
            int length = dataBaseColumnName.length;
            Field[] fields = new Field[length];
            Field[] declaredFields = clazz.getDeclaredFields();
            Method method;
            for (Field field : declaredFields) {
                method = getMethod(clazz, field);
                Column columnName = getColumn(method, field);
                if (columnName != null) {
                    int index = ArrayUtils.indexOf(dataBaseColumnName, columnName.name().trim().toUpperCase());
                    if (index != -1 && index < length) {
                        fields[index] = field;
                    }
                }
            }
            for (Object t : entitys) {
                List<Object> values = new ArrayList<Object>();
                for (Field field : fields) {
                    method = getMethod(clazz, field);
                    Column columnName = getColumn(method, field);
                    if (columnName != null) {
                        field.setAccessible(true);
                        Object value = field.get(t);
                        if (value != null) {
                            values.add(value);
                        }
                    }
                }
                batchArgs.add(values.toArray());
            }
        }
        return batchArgs;
    }

    /**
     * 获取批量更新对象集合中与数据库对应的字段的值
     *
     * @param entitys 实体对象集合
     * @return
     * @throws Exception
     */
    private List<Object[]> getBatchUpdateArgs(List<T> entitys, boolean isUpdateNullField) throws Exception {
        List<Object[]> batchArgs = new ArrayList<Object[]>();
        Class<?> clazz = null;
        if (entitys.size() > 0) {
            clazz = entitys.get(0).getClass();
        } else {
            return batchArgs;
        }
        if (clazz.isAnnotationPresent(Table.class)) {
            Field[] fields = clazz.getDeclaredFields();
            Method method;
            for (Object t : entitys) {
                Object idValue = null;// 主键值
                List<Object> values = new ArrayList<Object>();
                for (Field field : fields) {
                    method = getMethod(clazz, field);
                    Id id = getId(method, field);
                    Column columnName = getColumn(method, field);
                    if (columnName != null) {
                        field.setAccessible(true);
                        Object value = field.get(t);
                        if (value != null || isUpdateNullField) {
                            if (id != null) {
                                idValue = value;
                            } else {
                                values.add(value);
                            }
                        }
                    }
                }
                if (idValue == null) {
                    throw new Exception("无法更新,包含没有主键的记录!");
                }
                values.add(idValue);// 主键值放在最后作为修改的必要条件
                batchArgs.add(values.toArray());
            }
        }
        return batchArgs;
    }

    /**
     * @param method
     * @param field
     * @return
     */
    private Column getColumn(Method method, Field field) {
        Column columnName = null;
        if (method != null) {
            columnName = method.getAnnotation(Column.class);
        }
        if (columnName == null) {
            columnName = field.getAnnotation(Column.class);
        }
        return columnName;
    }

    /**
     * 获取属性对应的getter方法
     *
     * @param clazz 类型
     * @param field 属性
     * @return
     * @throws NoSuchMethodException
     */
    protected Method getMethod(Class<?> clazz, Field field) throws NoSuchMethodException {
        Method method = null;
        String fieldName = field.getName();// 实体类映射名
        int modifiers = field.getModifiers();
        if (Modifier.isStatic(modifiers) && Modifier.isFinal(modifiers)) {
            return method;
        }
        String methodSuffix = com.rybbaby.tss.core.utils.StringUtils.getGetterOrSetterSuffix(fieldName);
        String getterMentodName = "get" + methodSuffix;
        method = clazz.getMethod(getterMentodName);
        if (method == null) {
            if (fieldName.substring(0, 2).toLowerCase().equals("is")) {
                getterMentodName = "is" + methodSuffix;
                method = clazz.getMethod(getterMentodName);
            }
        }
        return method;
    }
}
